from django.core.management.base import BaseCommand
from django.db import connection, transaction

SQLS = [
    # 1) Add template_id if missing
    {
        "check": "SHOW COLUMNS FROM assets_labelprintjob LIKE 'template_id';",
        "when_missing": [
            "ALTER TABLE assets_labelprintjob ADD COLUMN template_id BIGINT NOT NULL;",
            # MySQL doesn't support IF NOT EXISTS for CREATE INDEX in older versions; rely on try/except.
            "CREATE INDEX assets_labelprintjob_template_id_idx ON assets_labelprintjob(template_id);",
            "ALTER TABLE assets_labelprintjob ADD CONSTRAINT fk_labelprintjob_template FOREIGN KEY (template_id) REFERENCES assets_labeltemplate(id) ON DELETE CASCADE;",
        ],
    },
    # 2) Drop legacy labels_per_page (or relax it if DROP fails)
    {
        "check": "SHOW COLUMNS FROM assets_labeltemplate LIKE 'labels_per_page';",
        "when_present": [
            "ALTER TABLE assets_labeltemplate DROP COLUMN labels_per_page;",
        ],
        "fallback": [
            "ALTER TABLE assets_labeltemplate MODIFY COLUMN labels_per_page INT NULL DEFAULT NULL;",
        ],
    },
    # 3) Drop other legacy columns removed by migration 0019
    # Boolean/text fields that may still linger in MySQL causing NOT NULL errors
    *[
        {
            "check": f"SHOW COLUMNS FROM assets_labeltemplate LIKE '{col}';",
            "when_present": [
                f"ALTER TABLE assets_labeltemplate DROP COLUMN {col};",
            ],
            "fallback": [
                # Attempt to relax type depending on typical type; Booleans -> TINYINT(1)
                # Text columns will be relaxed to TEXT NULL
                (
                    f"ALTER TABLE assets_labeltemplate MODIFY COLUMN {col} TINYINT(1) NULL DEFAULT NULL;"
                    if col in [
                        'include_qr','include_category','include_department','include_date',
                        'include_user','include_timestamp','is_default'
                    ] else f"ALTER TABLE assets_labeltemplate MODIFY COLUMN {col} TEXT NULL DEFAULT NULL;"
                ),
            ],
        }
        for col in [
            'include_qr',
            'include_category',
            'include_department',
            'include_date',
            'include_user',
            'include_timestamp',
            'footer_note',
            'title_prefix',
            'is_default',
        ]
    ],
]

class Command(BaseCommand):
    help = "Fix schema drift for label tables (LabelPrintJob.template_id and LabelTemplate.labels_per_page)."

    def handle(self, *args, **options):
        with connection.cursor() as cursor:
            for idx, item in enumerate(SQLS, start=1):
                check_sql = item.get("check")
                when_missing = item.get("when_missing", [])
                when_present = item.get("when_present", [])
                fallback = item.get("fallback", [])

                self.stdout.write(self.style.MIGRATE_HEADING(f"[Step {idx}] Running check: {check_sql}"))
                cursor.execute(check_sql)
                exists = cursor.fetchone() is not None

                if when_missing:
                    if not exists:
                        self.stdout.write(self.style.WARNING(" - Column missing. Applying fixes..."))
                        for s in when_missing:
                            try:
                                self.stdout.write(self.style.SQL_COLTYPE(f"   > {s}"))
                                cursor.execute(s)
                            except Exception as e:
                                self.stdout.write(self.style.WARNING(f"   ! Ignored error: {e}"))
                    else:
                        self.stdout.write(self.style.SUCCESS(" - Column already exists. Skipped."))

                if when_present:
                    if exists:
                        self.stdout.write(self.style.WARNING(" - Column present. Trying to drop..."))
                        try:
                            for s in when_present:
                                self.stdout.write(self.style.SQL_COLTYPE(f"   > {s}"))
                                cursor.execute(s)
                            self.stdout.write(self.style.SUCCESS(" - Dropped successfully."))
                        except Exception as e:
                            self.stdout.write(self.style.WARNING(f"   ! Drop failed: {e}"))
                            if fallback:
                                self.stdout.write(self.style.WARNING("   - Trying fallback (relax column)..."))
                                for s in fallback:
                                    try:
                                        self.stdout.write(self.style.SQL_COLTYPE(f"     > {s}"))
                                        cursor.execute(s)
                                    except Exception as e2:
                                        self.stdout.write(self.style.WARNING(f"     ! Fallback ignored error: {e2}"))
                    else:
                        self.stdout.write(self.style.SUCCESS(" - Column not present. Skipped."))

        self.stdout.write(self.style.SUCCESS("Schema fix completed."))
